package com.css.fxfzaqbz.modules.callJcData.repository.impl;


import cn.hutool.core.util.PageUtil;
import com.css.fxfzaqbz.base.response.RestResponse;
import com.css.fxfzaqbz.constants.YNEnum;
import com.css.fxfzaqbz.modules.base.entity.District;
import com.css.fxfzaqbz.modules.callJcData.entity.Gasholder;
import com.css.fxfzaqbz.modules.callJcData.param.DataVersionPageParam;
import com.css.fxfzaqbz.modules.callJcData.param.GasholderQueryParams;
import com.css.fxfzaqbz.modules.callJcData.param.SysDataVersionPageParam;
import com.css.fxfzaqbz.modules.callJcData.repository.GasholderRepository;
import com.css.fxfzaqbz.modules.supergraph.entity.CqgDataEntity;
import com.css.fxfzaqbz.modules.supergraph.vo.GasholderBrVO;
import com.css.fxfzaqbz.modules.supergraph.vo.SourceOfDangerBrVO;
import com.css.fxfzaqbz.util.PlatformDateUtils;
import com.css.fxfzaqbz.util.PlatformSessionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class GasholderRepositoryImpl implements GasholderRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public void save(Gasholder param) {
       /* (ga_id,ga_number,ga_name,ga_longitude,ga_latitude,ga_xh,ga_gcrj,ga_gzyl,ga_ttnj,ga_ttgd,ga_ttftbh," +
        "ga_ckzl,ga_jgcl,ga_dgcd,ga_dggd,ga_dgzfhd,ga_jcxs,ga_clxh,ga_txml,ga_qfyl," +
                "ga_xyyl,ga_cdlb,ga_ldsyhcd,ga_qdsyhcd,ga_bdsyhcd,ga_jdsyhcd,ga_ldszxcd,ga_qdszxcd," +
                "ga_bdszxcd,ga_jdszxcd,ga_jsnd,ga_sfld,del_flag,create_user,create_time,source_flag,data_version,)
        */

        String sql = "insert into sys_gasholder  values(" +
                ":gaId,:gaNumber,:gaName,:gaLongitude,:gaLatitude,:gaXh,:gaGcrj,:gaGzyl,:gaTtnj,:gaTtgd,:gaTtftbh," +
                ":gaCkzl,:gaJgcl,:gaDgcd,:gaDggd,:gaDgzfhd,:gaJcxs,:gaClxh,:gaTxml,:gaQfyl," +
                ":gaXyyl,:gaCdlb,:gaLdsyhcd,:gaQdsyhcd,:gaBdsyhcd,:gaJdsyhcd,:gaLdszxcd,:gaQdszxcd" +
                ",:gaBdszxcd,:gaJdszxcd,:gaJsnd,:gaSfld,:delFlag,:createUser,:createTime,:updateUser,:updateTime,:sourceFlag,:dataVersion,ST_GeometryFromText(:geom,4490),:province,:city,:county,:uploadTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public Gasholder findById(String id) {
        String sql = "";
        sql = "select *,ST_AsText(geom) geom from sys_gasholder  where  ga_id='" + id + "' and del_flag = '" + YNEnum.N.toString() + "'";
        Gasholder gasholder = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Gasholder.class));
        return gasholder;
    }

    @Override
    public void updateById(Gasholder param) {
        String sql = "update  sys_gasholder  set ga_number=:gaNumber , ga_name=:gaName,ga_longitude=:gaLongitude ,ga_latitude =:gaLatitude, ga_xh=:gaXh,ga_gcrj=:gaGcrj ,ga_gzyl=:gaGzyl ,ga_ttnj =:gaTtnj,ga_ttgd=:gaTtgd ,ga_ttftbh=:gaTtftbh" +
                ",ga_ckzl=:gaCkzl, ga_jgcl=:gaJgcl,ga_dgcd=:gaDgcd,ga_dgzfhd=:gaDgzfhd, ga_jcxs=:gaJcxs, ga_clxh=:gaClxh,ga_txml=:gaTxml,ga_qfyl=:gaQfyl," +
                "ga_xyyl=:gaXyyl, ga_cdlb=:gaCdlb,ga_ldsyhcd=:gaLdsyhcd,ga_qdsyhcd=:gaQdsyhcd, ga_bdsyhcd=:gaBdsyhcd,ga_jdsyhcd=:gaJdsyhcd, ga_ldszxcd=:gaLdszxcd,ga_qdszxcd=:gaQdszxcd," +
                "ga_bdszxcd=:gaBdszxcd, ga_jdszxcd=:gaJdszxcd,ga_jsnd=:gaJsnd,ga_sfld=:gaSfld, update_user=:updateUser," +
                "update_time=:updateTime" +
                (StringUtils.isBlank(param.getGeom()) ? " " : ", geom = ST_GeometryFromText(:geom,4490)") + " where  ga_id = :gaId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public void deleteById(String id) {
        Timestamp currentTimestamp = PlatformDateUtils.getCurrentTimestamp();
        String userId = PlatformSessionUtils.getUserId();
        String sql = "";
        sql = "update sys_gasholder  set del_flag ='" + YNEnum.Y.toString() + "',update_user='" + userId + "',update_time='" + currentTimestamp + "'where ga_id = '" + id + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<Gasholder> page(GasholderQueryParams param) {
        StringBuilder sqlBuilder = new StringBuilder("select * ,ST_AsText(geom) geom from  sys_gasholder  where 1=1 ");
        pageSQLBuilder(param, sqlBuilder);
        sqlBuilder.append("limit ").append(param.getRows()).append(" offset ").append(param.getPage());
        return jdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(Gasholder.class));
    }

    @Override
    public Integer countForPage(GasholderQueryParams param) {
        StringBuilder sqlBuilder = new StringBuilder("select count(*) from sys_gasholder where 1=1 ");
        pageSQLBuilder(param, sqlBuilder);
        return jdbcTemplate.queryForObject(sqlBuilder.toString(), Integer.class);
    }

    @Override
    public List<Gasholder> selectByIds(List<String> param) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT * ,ST_AsText(geom) geom from  sys_gasholder where ga_id IN ('");
        for (String id : param) {
            sqlBuilder.append(id).append("', '");
        }
        sqlBuilder.append("')");
        return jdbcTemplate.query(sqlBuilder.toString().replaceAll("\"", ""), new BeanPropertyRowMapper<>(Gasholder.class));

    }

    @Override
    public List<Gasholder> exportExcel() {
        return jdbcTemplate.query("select *,ST_AsText(geom) geom from sys_gasholder  where del_flag='"+ YNEnum.N.toString()+"'",new BeanPropertyRowMapper<>(Gasholder.class));
    }

    @Override
    public String getCount() {
        StringBuilder sqlBuilder = new StringBuilder("select count(*) from sys_gasholder where 1=1  ");
        try {
            Integer a = jdbcTemplate.queryForObject(sqlBuilder.toString(), Integer.class);
            return String.valueOf(a);
        } catch (DataAccessException e) {
            return null;
        }

    }

    @Override
    public List<Gasholder> getDataByTypeAndVersion(SysDataVersionPageParam sysDataVersionPageParam) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT *,ST_AsText(geom) geom from  sys_gasholder where 1=1  and del_flag='"+YNEnum.N.toString()+"'");
        if (sysDataVersionPageParam.getVersionCode()!=null){
            sqlBuilder.append("and source_flag = '" ).append(sysDataVersionPageParam.getDataRangeCountry()).append("' ");
        }
        if (StringUtils.isNotBlank(sysDataVersionPageParam.getVersionCode())){
            sqlBuilder.append("and data_version like '%"+ sysDataVersionPageParam.getVersionCode()+ "%' ");
        }
        if (sysDataVersionPageParam.getProvince()!=""){
            sqlBuilder.append("and province = '").append(sysDataVersionPageParam.getDataRangeProvince()).append("' ");
        }
        if (sysDataVersionPageParam.getCity()!=""){
            sqlBuilder.append("and city = '"+sysDataVersionPageParam.getCity()+"'");
        }
        if (sysDataVersionPageParam.getCounty()!=""){
            sqlBuilder.append("and county =  '"+sysDataVersionPageParam.getCounty()+"' ");
        }
        List<Gasholder> gasholders = jdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(Gasholder.class));

        return gasholders;
    }

    @Override
    public void byShpBatchSave(List<Gasholder> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "insert into sys_gasholder  values(" +
                ":gaId,:gaNumber,:gaName,:gaLongitude,:gaLatitude,:gaXh,:gaGcrj,:gaGzyl,:gaTtnj,:gaTtgd,:gaTtftbh," +
                ":gaCkzl,:gaJgcl,:gaDgcd,:gaDggd,:gaDgzfhd,:gaJcxs,:gaClxh,:gaTxml,:gaQfyl," +
                ":gaXyyl,:gaCdlb,:gaLdsyhcd,:gaQdsyhcd,:gaBdsyhcd,:gaJdsyhcd,:gaLdszxcd,:gaQdszxcd" +
                ",:gaBdszxcd,:gaJdszxcd,:gaJsnd,:gaSfld,:delFlag,:createUser,:createTime,:updateUser,:updateTime,:sourceFlag,:dataVersion,ST_GeometryFromText(:geom,4490),:province,:city,:county,:uploadTime,:zdNum)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void deleteByVersion(String versionCode) {
        String sql = "update sys_gasholder  set del_flag = '1' where data_version='" + versionCode + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void updateByLsVersion(String tempVersionCode,String dataVersion,Integer sf) {
        String sql = "update sys_gasholder  set del_flag = '0' ,data_version= '"+dataVersion+"' ,source_flag="+sf+"  where data_version='" + tempVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<Gasholder> getYwData(String versionCode) {
        StringBuilder stringBuilder=new StringBuilder("select *,ST_AsText(geom) geom from  sys_gasholder where 1=1 and data_version='"+versionCode+"' and del_flag='"+YNEnum.N.toString()+"' ");
        List<Gasholder> gasholders = null;
        try {
            gasholders = jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(Gasholder.class));
            return gasholders;
        } catch (DataAccessException e) {
          return null;
        }


    }

    @Override
    public void updateLs(String versionCode, String tempVersionCode) {
        String sql = "update sys_gasholder  set del_flag = '0' ,data_version= '"+versionCode+"'  where data_version='" + tempVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void saveGasholderBr(GasholderBrVO gasholderBrVO) {
        String sql = "insert into sys_gasholder_br  values (:id,:gcrj,:yxbj,ST_GeometryFromText(:geom,4490),:iaId,:gaLongitude,:gaLatitude,:delFlag,:dataVersion)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(gasholderBrVO);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public void deleteGasholderBr(String iaId,String dataVersion) {
        String sql = "delete  from sys_gasholder_br where ia_id='"+iaId+"' and data_version='"+dataVersion+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<Gasholder> getDataBySSQAndVersion(SourceOfDangerBrVO sourceOfDangerBrVO) {
            StringBuilder stringBuilder=new StringBuilder("select *,ST_AsText(geom) geom from  sys_gasholder where 1=1 and data_version='"+sourceOfDangerBrVO.getCqgDataVersion()+"' and del_flag='"+YNEnum.N.toString()+"' ");
            List<Gasholder> query = jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(Gasholder.class));
            return query;
    }

    @Override
    public Map<String, Object> getCqgDataVersion(CqgDataEntity param) {
        List<District> list = param.getList();
        List<String> xList=new ArrayList<>();
        List<String> sList=new ArrayList<>();
        List<String> shList=new ArrayList<>();
        for (District district : list) {
            if (district.getPrefecture()!=null){
                xList.add(district.getPrefecture());
            }
            if (district.getPrefecture()==null&&district.getCity()!=null){
                sList.add(district.getCity());
            }
            if (district.getCity()==null){
                shList.add(district.getProvince());
            }
        }
        Map<String,Object> paramMap=new HashMap<>();
        paramMap.put("xList",xList);
        paramMap.put("sList",sList);
        paramMap.put("shList",shList);
        StringBuilder sqlBuilder=new StringBuilder("select * from sys_data_version where 1=1 ");
        if (StringUtils.isNotBlank(param.getDataTypeCode())){
            sqlBuilder.append("and data_type_level1_code = '").append(param.getDataTypeCode()).append("' ");
        }
        if (shList.size()>0||shList!=null){
            sqlBuilder.append("and data_range_province in ( :shList)'");
        }
        if (sList.size()>0||sList!=null){
            sqlBuilder.append("and data_range_city in ( :sList)'");
        }

        if (xList.size()>0||xList!=null){
            sqlBuilder.append("and data_range_area in ( :xList)'");
        }
        int firstIndex=(param.getPage()-1) * param.getRows();
        int lastIndex=param.getRows();
        sqlBuilder.toString();
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        Integer total = namedParameterJdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(SysDataVersionPageParam.class)).size();
        List<SysDataVersionPageParam> sysDataVersionPageParams = namedParameterJdbcTemplate.query(sqlBuilder.append(limitSql).toString(), new BeanPropertyRowMapper<>(SysDataVersionPageParam.class));
   /*     for (SysDataVersionPageParam sysDataVersionPageParam : sysDataVersionPageParams) {

            SysDataVersion byVersionCode = getByVersionCode(sysDataVersionPageParam.getVersionCode());
            sysDataVersionPageParam.setDataDesc(byVersionCode.getDataDesc());
            sysDataVersionPageParam.setVersionDesc(byVersionCode.getVersionDesc());
        }*/
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",sysDataVersionPageParams);
        return map;
    }

    @Override
    public RestResponse getFyYwData(DataVersionPageParam dataVersionPageParam,String role) {
        StringBuilder sql = new StringBuilder("select *,ST_AsText(geom) geom from  sys_gasholder  where del_flag = '0' and data_version = '" + dataVersionPageParam.getVersionCode() +   " ' ");
        String userId = PlatformSessionUtils.getUserId();
      /*  if ("1".equals(role)){
            sql.append(" and (create_user ='" + userId + "' or leader_id like '%" + userId + "%' or technical_leader_id like '%" + userId + "%')");
        }*/

        if (!"0".equals(role)&&!"1".equals(role)){
            sql.append(" and  province = '" + role + "'  or source_flag = '2' " );
        }
        String limitSql= dataVersionPageParam.getPage() != null && dataVersionPageParam.getRows() != null ? " limit " + dataVersionPageParam.getRows() + " offset " + dataVersionPageParam.getPage() : "";
        List<Gasholder> query = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(Gasholder.class));
        String sql1 = "select count(*) from sys_gasholder where del_flag = '0' and data_version = '" + dataVersionPageParam.getVersionCode() + "'";
        Long aLong = jdbcTemplate.queryForObject(sql1, Long.class);
        return RestResponse.succeed(new HashMap<String, Object>() {{
           put("data", query);
            put("total", aLong);
            put("page", dataVersionPageParam.getPage());
            put("rows", dataVersionPageParam.getRows());
            put("totalPage", PageUtil.totalPage(aLong, dataVersionPageParam.getRows()));
        }});
    }

    @Override
    public List<Gasholder> getGasholderNumByVersion(String versionCode) {
        String sql="select *,ST_AsText(geom) geom from  sys_gasholder  where del_flag = '0' and data_version = '" + versionCode +   " ' ";
        List<Gasholder> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Gasholder.class));
        return query;
    }


    private void pageSQLBuilder(GasholderQueryParams param, StringBuilder sqlBuilder) {
        if (StringUtils.isNotBlank(param.getSourceFlag())) {
            sqlBuilder.append("and source_flag = '").append(param.getSourceFlag()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getDelFlag())) {
            sqlBuilder.append("and del_flag = '").append(param.getDelFlag()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getDataVersion())) {
            sqlBuilder.append("and data_version = '").append(param.getDataVersion()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getCreateUser())) {
            sqlBuilder.append("and create_user = '").append(param.getCreateUser()).append("' ");
        }
        if (param.getCreateTime() != null) {
            sqlBuilder.append("and create_time = '").append(param.getCreateTime()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getUpdateUser())) {
            sqlBuilder.append("and update_user = '").append(param.getUpdateUser()).append("' ");
        }
        if (param.getUpdateTime() != null) {
            sqlBuilder.append("and update_time = '").append(param.getUpdateTime()).append("' ");
        }
        if (param.getProvince()!=null){
            sqlBuilder.append("and province = '").append(param.getProvince()).append("' ");
        }
        if (param.getCity()!=null){
            sqlBuilder.append("and city = '").append(param.getCity()).append("' ");
        }
        if (param.getCounty()!=null){
            sqlBuilder.append("and county = '").append(param.getCounty()).append("' ");
        }
    /*    if (StringUtils.isNotBlank(param.getGeom())) {
            sqlBuilder.append("and geom = '").append(param.getGeom()).append("' ");
        }*/
    }
}
